/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Controleur.Excursion;
import Controleur.Moniteur;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author Valentin
 */
public class ExcursionDAO extends DAO<Excursion> {

    public ExcursionDAO(Connection conn) {
        super(conn);
    }

    @Override
    public void insert(Excursion excursion) throws SQLException {
        String query = " INSERT INTO"
                + " excursion"
                + " ( IDEmbarcation_fk, IDMoniteur_fk, Date, NombrePlaces, PrixPersonne)"
                + " VALUES (" + excursion.getIdEmbarcation() + "," + excursion.getMoniteur().getNumSecu() + ",\"" + excursion.getDate() + "\"," + excursion.getNbPlaces() + "," + excursion.getPrixPers() + ")";

        state.executeUpdate(query, state.RETURN_GENERATED_KEYS);
        try (ResultSet rs = state.getGeneratedKeys()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                do {
                    for (int i = 1; i <= columnCount; i++) {
                        int key = rs.getInt(i);
                        excursion.setId(key);
                    }
                } while (rs.next());
            } else {
                excursion.setId(-1);
            }
        }
                
        query = " INSERT INTO"
                + " etape"
                + " ( IDEmbarcadere_fk, "
                + " IDExcursion_fk, "
                + " Heure, "
                + " TypeEtape)"
                + " VALUES (" + excursion.getEmbarcadereDepart().getId()+ "," 
                +  excursion.getId() + ",\"" 
                +  excursion.getEmbarcadereDepart().getHeurePassage() + "\"," 
                +  "depart)";

        state.executeUpdate(query);
        
        query = " INSERT INTO"
                + " etape"
                + " ( IDEmbarcadere_fk, "
                + " IDExcursion_fk, "
                + " Heure, "
                + " TypeEtape)"
                + " VALUES (" + excursion.getEmbarcadereArrivee().getId()+ "," 
                +  excursion.getId() + ",\"" 
                +  excursion.getEmbarcadereArrivee().getHeurePassage() + "\"," 
                +  "depart)";

        state.executeUpdate(query);
    }

    @Override
    public void delete(Excursion excursion) throws SQLException {
        String query = " DELETE FROM"
                + " excursion"
                + " WHERE IDExcursion ="
                + excursion.getId();

        state.executeUpdate(query);
    }

    @Override
    public void update(Excursion excursion) throws SQLException {
        String query = " UPDATE  excursion"
                + " SET IDEmbarcation_fk = " + excursion.getIdEmbarcation()
                + " ,IDMoniteur_fk = " + excursion.getMoniteur().getNumSecu()
                + " ,Date = \"" + excursion.getDate()
                + "\" ,NombrePlaces = " + excursion.getNbPlaces()
                + " ,PrixPersonne = " + excursion.getPrixPers()
                + " WHERE IDExcursion = " + excursion.getId();

        state.executeUpdate(query);
    }

    @Override
    public Excursion findByID(java.lang.Integer id) throws SQLException {
        DAO<Moniteur> moniteurDAO = new MoniteurDAO(connexion);
        ParticipantDAO participantsDAO = new ParticipantDAO(connexion);
        EmbarcadereDAO embarcadereDAO = new EmbarcadereDAO(connexion);

        Excursion myExcursion = new Excursion();
        String query = " SELECT *"
                + " FROM excursion ex"
                + " JOIN embarcation em on(em.IDEmbarcation = ex.IDEmbarcation_fk)  "
                + " WHERE IDExcursion = "
                + id;


        queryResult = state.executeQuery(query);
        while (queryResult.next()) {

            myExcursion.setId(queryResult.getInt("IDParticipant"));
            myExcursion.setDate(queryResult.getDate("Date"));
            myExcursion.setIdEmbarcation(queryResult.getInt("IDEmbarcation_fk"));
            myExcursion.setEmbarcation(queryResult.getString("embarcation.Nom"));
            myExcursion.setNbPlaces(queryResult.getInt("NombrePlaces"));
            myExcursion.setPrixPers(Integer.decode(queryResult.getString("PrixPersonne")));
            myExcursion.setMoniteur(moniteurDAO.findByID(queryResult.getInt("IDMoniteur_fk")));
            myExcursion.setParticipants(participantsDAO.findByExcursion(queryResult.getInt("IDExcursion")));
            myExcursion.setEmbarcadereDepart(embarcadereDAO.findEmbarcadereOfExcursion("depart", myExcursion.getId()));
            myExcursion.setEmbarcadereArrivee(embarcadereDAO.findEmbarcadereOfExcursion("arrivee", myExcursion.getId()));
        }

        return myExcursion;
    }

    public Map<Integer, Excursion> findByDate(Date aDate) throws SQLException {
        Map myMap = new HashMap();
        String query = " SELECT *"
                + " FROM excursion ex"
                + " left JOIN embarcation em ON (ex.IDEmbarcation_fk = em.IDEmbarcation)"
                + " left JOIN etape et ON (et.IDExcursion_fk = ex.IDExcursion)"
                + " left JOIN embarcadere embarc ON (embarc.IDEmbarcadere = et.IDEmbarcadere_fk)"
                + " WHERE ex.Date = "
                + aDate;

        queryResult = state.executeQuery(query);
        DAO<Moniteur> moniteurDAO = new MoniteurDAO(connexion);
        ParticipantDAO participantsDAO = new ParticipantDAO(connexion);
        EmbarcadereDAO embarcadereDAO = new EmbarcadereDAO(connexion);

        while (queryResult.next()) {
            Excursion myExcursion = new Excursion();
            myExcursion.setId(queryResult.getInt("IDExcursion"));
            myExcursion.setEmbarcation(queryResult.getString("embarcation.Nom"));
            myExcursion.setIdEmbarcation(queryResult.getInt("IDEmbarcation_fk"));
            myExcursion.setNbPlaces(queryResult.getInt("NombrePlaces"));
            myExcursion.setPrixPers(Integer.decode(queryResult.getString("PrixPersonne")));
            myExcursion.setDate(queryResult.getDate("Date"));
            myExcursion.setMoniteur(moniteurDAO.findByID(queryResult.getInt("IDMoniteur_fk")));
            myExcursion.setParticipants(participantsDAO.findByExcursion(queryResult.getInt("IDExcursion")));
            myExcursion.setEmbarcadereDepart(embarcadereDAO.findEmbarcadereOfExcursion("depart", myExcursion.getId()));
            myExcursion.setEmbarcadereArrivee(embarcadereDAO.findEmbarcadereOfExcursion("arrivee", myExcursion.getId()));

            myMap.put(myExcursion.getId(), myExcursion);
        }

        return myMap;

    }

    @Override
    public Map<Integer, Excursion> findAll() throws SQLException {
        Map myMap = new HashMap();
        String query = " SELECT *"
                + " FROM excursion ex"
                + " left JOIN embarcation em ON (ex.IDEmbarcation_fk = em.IDEmbarcation)";



        queryResult = state.executeQuery(query);
        DAO<Moniteur> moniteurDAO = new MoniteurDAO(connexion);
        ParticipantDAO participantsDAO = new ParticipantDAO(connexion);
        EmbarcadereDAO embarcadereDAO = new EmbarcadereDAO(connexion);

        while (queryResult.next()) {
            Excursion myExcursion = new Excursion();


            myExcursion.setId(queryResult.getInt("IDExcursion"));
            myExcursion.setEmbarcation(queryResult.getString("Nom"));
            myExcursion.setIdEmbarcation(queryResult.getInt("IDEmbarcation_fk"));
            myExcursion.setNbPlaces(queryResult.getInt("NombrePlaces"));
            myExcursion.setPrixPers(Integer.decode(queryResult.getString("PrixPersonne")));
            myExcursion.setDate(queryResult.getDate("Date"));
            myExcursion.setMoniteur(moniteurDAO.findByID(queryResult.getInt("IDMoniteur_fk")));
            myExcursion.setParticipants(participantsDAO.findByExcursion(queryResult.getInt("IDExcursion")));
            myExcursion.setEmbarcadereDepart(embarcadereDAO.findEmbarcadereOfExcursion("depart", myExcursion.getId()));
            myExcursion.setEmbarcadereArrivee(embarcadereDAO.findEmbarcadereOfExcursion("arrivee", myExcursion.getId()));
            myMap.put(myExcursion.getId(), myExcursion);
        }

        return myMap;

    }
}
